package xyz.lwm.lazycat.jdbc.exec;

import lombok.extern.slf4j.Slf4j;
import org.jetbrains.annotations.Nullable;
import xyz.lwm.lazycat.jdbc.conf.JdbcProperties;
import xyz.lwm.lazycat.setting.Setting;
import xyz.lwm.lazycat.utility.StringUtil;
import xyz.lwm.lazycat.utility.convert.Convert;

import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.time.LocalDateTime;
import java.time.ZoneId;

@Slf4j
public abstract class JdbcUtil {

	private static final boolean printParam = Setting.get().forPath(JdbcProperties.class).isPrintParams();

	/**
	 * Close the given JDBC Connection and ignore any thrown exception.
	 * This is useful for typical finally blocks in manual JDBC code.
	 * @param con the JDBC Connection to close (may be {@code null})
	 */
	public static void closeConnection(@Nullable Connection con) {
		if (con != null) {
			try {
				con.close();
			} catch (SQLException ex) {

				log.debug("Could not close JDBC Connection", ex);
			} catch (Throwable ex) {
				// We don't trust the JDBC driver: It might throw RuntimeException or Error.
				log.debug("Unexpected exception on closing JDBC Connection", ex);
			}
		}
	}

	/**
	 * Close the given JDBC Statement and ignore any thrown exception.
	 * This is useful for typical finally blocks in manual JDBC code.
	 * @param stmt the JDBC Statement to close (may be {@code null})
	 */
	public static void closeStatement(@Nullable Statement stmt) {
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException ex) {
				log.trace("Could not close JDBC Statement", ex);
			} catch (Throwable ex) {
				// We don't trust the JDBC driver: It might throw RuntimeException or Error.
				log.trace("Unexpected exception on closing JDBC Statement", ex);
			}
		}
	}

	/**
	 * Close the given JDBC ResultSet and ignore any thrown exception.
	 * This is useful for typical finally blocks in manual JDBC code.
	 * @param rs the JDBC ResultSet to close (may be {@code null})
	 */
	public static void closeResultSet(@Nullable ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException ex) {
				log.trace("Could not close JDBC ResultSet", ex);
			} catch (Throwable ex) {
				// We don't trust the JDBC driver: It might throw RuntimeException or Error.
				log.trace("Unexpected exception on closing JDBC ResultSet", ex);
			}
		}
	}

	public static String lookupColumnName(ResultSetMetaData resultSetMetaData, int columnIndex) throws SQLException {
		String name = resultSetMetaData.getColumnLabel(columnIndex);
		if (StringUtil.isEmpty(name)) {
			name = resultSetMetaData.getColumnName(columnIndex);
		}
		return name;
	}

	/**
	 * Retrieve a JDBC column value from a ResultSet.
	 * {@code java.sql.Timestamp} object.
	 *
	 * @param rs    is the ResultSet holding the data
	 * @param index is the column index
	 * @return the value object
	 * @throws java.sql.SQLException if thrown by the JDBC API
	 * @see java.sql.Blob
	 * @see java.sql.Clob
	 * @see java.sql.Timestamp
	 */
	@Nullable
	public static Object getResultSetValue(ResultSet rs, int index) throws SQLException {
		Object obj = rs.getObject(index);
		String className = null;
		if (obj != null) {
			className = obj.getClass().getName();
		}
		if (obj instanceof Blob) {
			Blob blob = (Blob) obj;
			obj = blob.getBytes(1, (int) blob.length());
		} else if (obj instanceof Clob) {
			Clob clob = (Clob) obj;
			obj = clob.getSubString(1, (int) clob.length());
		} else if ("oracle.sql.TIMESTAMP".equals(className) || "oracle.sql.TIMESTAMPTZ".equals(className)) {
			obj = rs.getTimestamp(index);
		} else if (className != null && className.startsWith("oracle.sql.DATE")) {
			String metaDataClassName = rs.getMetaData().getColumnClassName(index);
			if ("java.sql.Timestamp".equals(metaDataClassName) || "oracle.sql.TIMESTAMP".equals(metaDataClassName)) {
				obj = rs.getTimestamp(index);
			} else {
				obj = rs.getDate(index);
			}
		} else if (obj instanceof Date) {
			if ("java.sql.Timestamp".equals(rs.getMetaData().getColumnClassName(index))) {
				obj = rs.getTimestamp(index);
			}
		} else if (obj instanceof LocalDateTime) {
			obj = toDate((LocalDateTime) obj);
		} else if (obj instanceof Timestamp) {
			obj = new java.util.Date(((Timestamp) obj).getTime());
		}
		return obj;
	}

	private static java.util.Date toDate(LocalDateTime localDateTime) {
		return java.util.Date.from(localDateTime.atZone(ZoneId.systemDefault()).toInstant());
	}

	public static boolean supportsBatchUpdates(Connection con) {
		try {
			DatabaseMetaData dbmd = con.getMetaData();
			if (dbmd != null) {
				if (dbmd.supportsBatchUpdates()) {
					log.debug("JDBC driver supports batch updates");
					return true;
				} else {
					log.debug("JDBC driver does not support batch updates");
				}
			}
		} catch (SQLException ex) {
			log.debug("JDBC driver 'supportsBatchUpdates' method threw exception", ex);
		}
		return false;
	}

	public static Object toParamValue(int type, Object o) {
		if (o == null) {
			return o;
		} else if (o instanceof CharSequence) {

			if (type == Types.TIMESTAMP) {
				return Convert.to(o, Timestamp.class);
			} else if (type == Types.DATE) {
				return Convert.to(o, Date.class);
			} else if (type == Types.TIME) {
				return Convert.to(o, Time.class);
			}

		} else if (o instanceof Number) {

			if (type == Types.INTEGER) {
				return Integer.valueOf(o.toString());
			} else if (type == Types.BIGINT) {
				return Long.valueOf(o.toString());
			} else if (type == Types.FLOAT) {
				return Float.valueOf(o.toString());
			} else if (type == Types.DOUBLE) {
				return Double.valueOf(o.toString());
			} else if (type == Types.SMALLINT) {
				return Short.valueOf(o.toString());
			} else if (type == Types.TINYINT) {
				return Byte.valueOf(o.toString());
			} else if (type == Types.DECIMAL) {
				return new BigDecimal(o.toString());
			}

		} else if (o instanceof java.util.Date) {

			if (type == Types.DATE) {
				return new Date(((java.util.Date) o).getTime());
			} else if (type == Types.TIME) {
				return new Time(((java.util.Date) o).getTime());
			} else if (type == Types.TIMESTAMP) {
				return new Timestamp(((java.util.Date) o).getTime());
			}

		}

		return o;
	}

	public static boolean isNumeric(int sqlType) {
		return (Types.BIT == sqlType || Types.BIGINT == sqlType || Types.DECIMAL == sqlType ||
				Types.DOUBLE == sqlType || Types.FLOAT == sqlType || Types.INTEGER == sqlType ||
				Types.NUMERIC == sqlType || Types.REAL == sqlType || Types.SMALLINT == sqlType ||
				Types.TINYINT == sqlType);
	}

	public static void setValue(PreparedStatement ps, ParameterMetaData pmd, int paramIndex, @Nullable Object inValue) {
		try {
			String url = ps.getConnection().getMetaData().getURL();
			if (inValue == null) {
				ps.setNull(paramIndex, Types.NULL);
			} else
				// we cannot get parameter type in oracle from ParameterMetaData
				if (url.contains("oracle")) {
					// oracle
					if (inValue instanceof CharSequence) {
						ps.setString(paramIndex, inValue.toString());

					} else if (inValue instanceof java.util.Date) {
						ps.setTimestamp(paramIndex, new Timestamp(((java.util.Date) inValue).getTime()));

					} else {
						// Fall back to generic setObject call without SQL type specified.
						ps.setObject(paramIndex, inValue);
					}
					if (printParam) {
						log.info("Sql parameter at index {} is '{}' [{}]", paramIndex, pmd.getParameterTypeName(paramIndex), inValue);
					}

				} else {
					Object paramValue = JdbcUtil.toParamValue(pmd.getParameterType(paramIndex), inValue);
					if (printParam) {
						log.info("Sql parameter at index {} is '{}' [{}]", paramIndex, pmd.getParameterTypeName(paramIndex), paramValue);
					}
					ps.setObject(paramIndex, paramValue);
				}

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

}
